Indexed Table Joins

If you are here, then you have a SQL that is not using an index. You have already:

• Checked that the index exists
• Checked the table and index have been analysed
• Used the INDEX hint to force Oracle to use the index with a UNIQUE or RANGE scan, but failed.

Indexed Table Joins are useful when we have identified a small number of rows in one table, and we want to join them to a small number of rows in a second table using an index on the second table to identify matching rows.

Consider the following SQL

SELECT b.*
FROM order a, order_line b
WHERE a.cust_id = 1244
AND   a.order_id = b.order_id

Let's assume the following:

• ORDER and ORDER_LINE are large tables
• ORDER.CUST_ID is fairly selective (<4% of ORDER per distinct value)
• ORDER_ID has high cardinality (<10 rows in ORDER_LINE per distinct value)
• ORDER_LINE.ORDER_ID is indexed

The best result for this SQL is to read ORDER retrieving rows where CUST_ID = 1244 (an index on CUST_ID will help, but is not necessary for the join), then for each row use an index to lookup ORDER_LINE on each ORDER_ID.

So what happens if Oracle doesn't do it this way? Some possibilities are:

• The tables are Hash joined. This will usually happen when statistics are not calculated, or if CUST_ID has poor selectivity.
• The tables are processed in the reverse order (table_b, then table_a). This could happen if the index on ORDER_LINE.ORDER_ID is missing.

If statistics on the tables are up to date and we are determined to use the index on ORDER_LINE, then we must do three things to counteract the Cost Based Optimizer:

• Add an ORDERED hint to force the join order.
• Add a USE_NL hint to force the use of a Nested Loops join.
• Add an INDEX hint to force the use of an index.

SELECT /*+ ORDERED USE_NL(b) INDEX(b)*/ b.*
FROM order a, order_line b
WHERE a.cust_id = 1244
AND   a.order_id = b.order_id

Check the execution plan with Explain Plan.

• Is it joining the tables in the correct order?
  If not, check the spelling of your ORDERED hint. Also ensure that the join is not an Outer Join; Outer Joins must start with the inner table and join to the outer table.
• Is it using a Nested Loops join?
  If not, check your USE_NL hint. You must use the table alias in the hint, not the table name (unless you have not used an alias).
• If the join order and join method are correct, is it using a different index on table_b? Try adding the index name to the INDEX hint.

Is it using the index now? If not, then the index is somehow unusable - see your DBA. Even if it is now using the index, the Cost Based Optimiser thinks there is a better way. Do it the courtesy of comparing the SQL with and without hints.

Joining to a hash clustered table

Consider the same example above, except this time order_line is hash clustered on order_id. This can be quite a sensible approach, as it ensures that all lines for an order will be stored in the same database block. If the hash cluster is well designed, then it can be more efficient to join to order_line using a hash cluster scan rather than an index scan.

The process for forcing a hash cluster scan on one table in the join is almost identical to the process described above for Indexed Joins, except the HASH hint is used in place of the INDEX hint.